﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.IO;
using MySql.Data.MySqlClient;
using System.Net;

namespace SnakeServer
{
    class DataHandler
    {
        public static string connectstr = "Database=snake;datasource=127.0.0.1;port=3306;user=root;pwd=1346520wbn;SslMode = none;";

        public static int GetBarrierCount()
        { 
            MySqlConnection connection = new MySqlConnection(connectstr);
            connection.Open();

            int count = 0;
            MySqlCommand command = new MySqlCommand("select * from barriers", connection);
            MySqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                count++;
            }
            reader.Close();
            command.Dispose();
            connection.Close();
            return count;
        }

        public static string RegistAccount(string username, string password)
        {
            
            MySqlConnection connection = new MySqlConnection(connectstr);
            connection.Open();

            bool usernameexitflag = false;
            MySqlCommand command = new MySqlCommand("select * from users", connection);
            MySqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                string name = reader.GetString("name");
                if (name == username)
                {
                    usernameexitflag = true;
                    break;
                }
            }
            reader.Close();
            command.Dispose();
            if (usernameexitflag)
            {
                connection.Close();
                return "用户名已存在！请重新注册！";
            }
            else
            {
                long time = (DateTime.Now.ToUniversalTime().Ticks - 621355968000000000) / 10000000;
                MySqlCommand mycommand = new MySqlCommand("insert into users set name='" + username + "'" + ",password='" + password + "'" + ",level=" + 1 + ",gold=" + 50 + ",exp=" + 0 + ",time=" + time, connection);
                mycommand.ExecuteNonQuery();
                Console.WriteLine("用户创建了用户名为" + username + "在" + DateTime.Now.ToString());
                mycommand.Dispose();
                connection.Close();
                return "OK";
            }
        }

        public static string Login(string username, string password)
        {
            MySqlConnection connection = new MySqlConnection(connectstr);
            connection.Open();

            bool usernameexitflag = false;
            bool userpwdflag = true;

            MySqlCommand command = new MySqlCommand("select * from users", connection);
            MySqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                string name = reader.GetString("name");
                if (name == username)
                {
                    usernameexitflag = true;
                    string pwd = reader.GetString("password");
                    if (pwd != password)
                    {
                        userpwdflag = false;
                    }
                    break;
                }
            }
            reader.Close();
            connection.Close();
            if (usernameexitflag && userpwdflag)
            {
                Console.WriteLine(username + "登陆成功::" + DateTime.Now.ToString());
                return "OK";
            }
            else
            {
                if (!usernameexitflag) return "用户名不存在";
                return "用户名或密码错误";
            }
        }

        public static string UpLoadBarrier(string name,string author,int dif,string json)
        {
            MySqlConnection connection = new MySqlConnection(connectstr);
            connection.Open();

            bool nameexitflag = false;
            MySqlCommand command = new MySqlCommand("select * from barriers", connection);
            MySqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                string nm = reader.GetString("name");
                if (nm == name)
                {
                    nameexitflag = true;
                    break;
                }
            }
            reader.Close();
            command.Dispose();
            if (nameexitflag)
            {
                connection.Close();
                return "关卡名已存在！请更改名称！";
            }
            else
            {
                long time = (DateTime.Now.ToUniversalTime().Ticks - 621355968000000000) / 10000000;
                MySqlCommand mycommand = new MySqlCommand("insert into barriers set name='" + name + "'" + ",author='" + author + "'" + ",difficulty=" + dif + ",json='" + json + "',time=" + time, connection);
                mycommand.ExecuteNonQuery();
                Console.WriteLine(author + "提交了一张名为" + name + "的地图\n难度：\n" + dif.ToString() + "\n时间：" + DateTime.Now.ToString());
                mycommand.Dispose();
                connection.Close();
                return "OK";
            }
        }

        public static string ProvideBarrier(int id)
        {
            MySqlConnection connection = new MySqlConnection(connectstr);
            connection.Open();

            MySqlCommand command = new MySqlCommand("select * from barriers", connection);
            MySqlDataReader reader = command.ExecuteReader();

            while(reader.Read())
            {
                int bid = reader.GetInt32("id");
                if(bid==id)
                {
                    string json = reader.GetString("json");
                    reader.Close();
                    connection.Close();
                    return json;
                }
            }
            reader.Close();
            connection.Close();
            return null;
        }
        //自己改哇
        #region 待修改
        public static string SetRanking(int bid,int uid,int score ,int snakelen)
        {
            MySqlConnection connection = new MySqlConnection(connectstr);
            connection.Open();

            long time = (DateTime.Now.ToUniversalTime().Ticks - 621355968000000000) / 10000000;
            MySqlCommand command = new MySqlCommand("insert into gamerecord set barrierid=" + bid + ",userid=" + uid + ",score=" + score + ",len=" + snakelen+",time="+time,connection);
            command.ExecuteNonQuery();

            MySqlCommand usercmd = new MySqlCommand("select * from users", connection);
            MySqlDataReader reader = usercmd.ExecuteReader();
            while (reader.Read())
            {
                int id = reader.GetInt32("id");
                if(id==uid)
                {
                    string username = reader.GetString("name");
                    Console.WriteLine(username + "在第" + bid.ToString() + "关取得了" + score.ToString() + "分长度为" + snakelen.ToString() + "的惊人成绩！时间：" + DateTime.Now.ToString());
                }
            }
            return "OK";
        }

        public static int ReturnUserid(string username)
        {
            MySqlConnection connection = new MySqlConnection(connectstr);
            connection.Open();

            MySqlCommand command = new MySqlCommand("select * from users", connection);
            MySqlDataReader reader = command.ExecuteReader();

            int userid = -1;
            while (reader.Read())
            {
                string name = reader.GetString("name");
                if (name == username)
                {
                    userid = reader.GetInt32("id");
                    break;
                }
            }
            return userid;
        }

        public static string ReadRanking(int bid)
        {
            MySqlConnection connection = new MySqlConnection(connectstr);
            connection.Open();

            MySqlCommand cmd = new MySqlCommand("select * from gamerecord where barrierid='" + bid + "'", connection);
            MySqlDataReader reader = cmd.ExecuteReader();

            StringBuilder resultb = new StringBuilder("");
            bool firstflag = true;
            while(reader.Read())
            {
                int uid = reader.GetInt32("userid");
                int score = reader.GetInt32("score");
                int len = reader.GetInt32("len");
                int time = reader.GetInt32("time");
                if (firstflag)
                {
                    firstflag = false;
                    resultb.Append(uid + "|" + score + "|" + len + "|" + time);
                    continue;
                }
                resultb.Append("%" + uid + "|" + score + "|" + len + "|" + time);
            }
            return resultb.ToString();
        }

        public static string ReadBarrierList()
        {
            MySqlConnection connection = new MySqlConnection(connectstr);
            connection.Open();

            MySqlCommand cmd = new MySqlCommand("select * from barriers", connection);
            MySqlDataReader reader = cmd.ExecuteReader();

            Console.WriteLine("接收到用户获取关卡列表请求...");

            StringBuilder result = new StringBuilder("");
            while (reader.Read())
            {
                int id = reader.GetInt32("id");
                string name = reader.GetString("name");
                result.Append(id + ")" + name+"%");
            }
            var res = result.ToString();
            Console.WriteLine(result.ToString());

            return result.ToString();
        }

        public static string ReadBarrierInfo(int bid)
        {
            MySqlConnection connection = new MySqlConnection(connectstr);
            connection.Open();

            MySqlCommand cmd = new MySqlCommand("select * from barriers where id=" + bid, connection);
            MySqlDataReader reader = cmd.ExecuteReader();

            string result = "";
            reader.Read();
            string author = reader.GetString("author");
            int dif = reader.GetInt32("difficulty");
            string info = reader.GetString("info");
            result = author + "%" + dif.ToString() + "%" + info;
            return result;
        }

        public static string ReturnUsername(int id)
        {
            MySqlConnection connection = new MySqlConnection(connectstr);
            connection.Open();

            MySqlCommand command = new MySqlCommand("select * from users", connection);
            MySqlDataReader reader = command.ExecuteReader();

            string username = "";
            while (reader.Read())
            {
                int uid = reader.GetInt32("id");
                if (id==uid)
                {
                    username = reader.GetString("name");
                    break;
                }
            }
            return username;
        }
    }
    #endregion
}
